<?php
 if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mcoupon extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function getData()
    {
        $index = $this->getParamInt('index');
        if(!in_array($index, array(null, '', '0')))
        {
            $limit_sql = " LIMIT  ".$index.", 10";
        }else{
            $limit_sql = " LIMIT  10";
        }
        
        $sql = 'SELECT ecp.id AS ecpid, ecp.title AS ecptitle, ecp.img_qrcode as img_qrcode, ecp.img_thumbnail AS img_thumbnail, ecp.sale_off, ecp.amount,ecp.type_sl,  FROM_UNIXTIME(ecp.date_user, "%d/%m/%Y") AS expiration_date, ecp.`condition`, ecp.content_sale_off AS short_description FROM ecoupon AS ecp WHERE ecp.active = 1';
        
		$num_rows = $this->db->query($sql.$limit_sql)->num_rows();
		if($num_rows == 0)
		{
			return (object)array('status' => 404,'content' => array(), 'description' => 'Danh sách coupon rỗng');
		}
        $runSql = $this->db->query($sql.$limit_sql)->result_object();
        
        foreach($runSql AS $el=>$le)
        {
			$runSql[$el]->ecptitle = stripslashes($le->ecptitle);
			$runSql[$el]->short_description = stripslashes($le->short_description);
			
			if(in_array($le->condition, array(null, '', '0')))
			{
				$runSql[$el]->condition = '';
			}else{
				$runSql[$el]->condition = $le->condition;
			}
            if(!in_array($le->ecpid, array(null, '', '0')) && $le->type_sl != 0)
            {
                //die('SELECT CONCAT(r.`name`,": ",ra.address) AS address FROM ecoupon_key AS eck INNER JOIN restaurant AS r ON r.id = eck.id_restaurant INNER JOIN restaurant_address AS ra ON ra.restaurant_id = r.id WHERE eck.id_ecoupon = '.$le->ecpid);
                $runSql[$el]->_address = $this->db->query('SELECT CONCAT(r.`name`,": ", ra.address) AS address FROM ecoupon_key AS eck  INNER JOIN restaurant AS r ON r.id = eck.id_restaurant RIGHT JOIN restaurant_address AS ra ON ra.restaurant_id = r.id WHERE eck.id_ecoupon = '.$le->ecpid)->result_object();
            }else{
                $runSql[$el]->_address = (object)array();
            }
        }
        return (object)array('status' => 200,'content' => $runSql, 'description' => 'Danh sách coupon');
    }
    
    public function getDetail()
    {
        $id = $this->getParamInt('id');
        $user_id = $this->getParamInt('user_id');
        $sql = 'SELECT ecp.id AS ecpid, ecp.title AS ecptitle, ecp.code_coupon,ecp.img_qrcode, ecp.img_thumbnail AS img_thumbnail, ecp.amount_available, ecp.content, ecp.content_sale_off AS brief, ecp.sale_off, ecp.amount,ecp.type_sl,  FROM_UNIXTIME(ecp.date_user, "%d/%m/%Y") AS expiration_date, ecp.`condition` FROM ecoupon AS ecp WHERE ecp.active = 1 AND ecp.id = '.$id;
		$num_rows = $this->db->query($sql)->num_rows();
		if($num_rows == 0)
		{
			return (object)array('status' => 404,'content' => array(), 'description' => 'Không tồn tại coupon này', 'data_cp' => array());
		}
        $runSql = $this->db->query($sql)->result_object();
        
        foreach($runSql AS $el=>$le)
        {
			$runSql[$el]->ecptitle = stripslashes($le->ecptitle);
			$runSql[$el]->brief = stripslashes($le->brief);
			
            if(!in_array($le->ecpid, array(null, '', '0')) && $le->type_sl != 0)
            {
                //die('SELECT CONCAT(r.`name`,": ",ra.address) AS address FROM ecoupon_key AS eck INNER JOIN restaurant AS r ON r.id = eck.id_restaurant INNER JOIN restaurant_address AS ra ON ra.restaurant_id = r.id WHERE eck.id_ecoupon = '.$le->ecpid);
                $runSql[$el]->_address = $this->db->query('SELECT CONCAT(r.`name`,": ", ra.address) AS address FROM ecoupon_key AS eck  INNER JOIN restaurant AS r ON r.id = eck.id_restaurant RIGHT JOIN restaurant_address AS ra ON ra.restaurant_id = r.id WHERE eck.id_ecoupon = '.$le->ecpid)->result_object();
            }else{
                $runSql[$el]->_address = (object)array();
            }
        }        
        if(in_array($runSql[0]->condition, array(null, '', '0')))
		{
			$runSql[0]->condition = '';
		}
		
		if( in_array($user_id,array('',null,0,'0')) ){
			return (object)array('status' => 200,'content' => $runSql, 'description' => 'Chi tiết coupon', 'data_cp' => array());
		}else{
			$data_ = $this->db->query('SELECT * FROM ecoupon_user WHERE user_id = '.$user_id.' AND coupon_id = '.$id.' AND active = 1 ');
			if( $data_->num_rows() > 0 ){
				$data_cp = array( 'user_id' => $user_id, 'code_coupon' => $data_->row()->code_coupon, 'active' => $data_->row()->active, 'created' => $data_->row()->created );
				return (object)array('status' => 201,'content' => $runSql, 'description' => 'Chi tiết coupon', 'data_cp' => $data_cp);
			}else{
				return (object)array('status' => 200,'content' => $runSql, 'description' => 'Chi tiết coupon', 'data_cp' => array() );
			}
		}
    }

    public function reviewCouponDB($coupon_id,$user_id,$name,$star,$content){
        
        $check = $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id = '.$user_id)->num_rows();
        if($check > 0)
        {
            $checkUser = $this->db->query('SELECT id FROM member WHERE id = '.$user_id.' AND active = 2')->num_rows();
            if($checkUser > 0)
            {
                return (object)array('status' => 203, 'description' => 'Tài khoản này đang bị khóa');
            }
            
            $data = array (
                'coupon_id'     => $coupon_id,
                'user_id'       => $user_id,
                'name'          => $name,
                'star'          => $star,
                'content'       => $content,
                'created'       => time(),
                'improved'      => 0
                );
                
            $run = $this->db->insert('ecoupon_review', $data);
            if($run){
                return (object)array('status' => 200, 'description' => 'Thêm thành công');
            }else{
                return (object)array('status' => 201, 'description' => 'Thêm thất bại');
            }
        }else{
            return (object)array('status' => 202, 'description' => 'Người dùng chưa sử dụng coupon này');
        }
        
    }
    
    public function getReviewCoupon(){
        
        $id = $this->getParamInt('id');
        $index = $this->getParamInt('index');
        if(!in_array($index, array(null, '', '0')))
        {
            $limit_sql = " LIMIT  ".$index.", 10";
        }else{
            $limit_sql = " LIMIT  10";
        }
        
        $sql = $this->db->query('SELECT er.`name`, er.star, er.content, m.username, m.fullname FROM ecoupon_review AS er INNER JOIN member AS m ON er.user_id = m.id WHERE er.improved = 1 AND er.coupon_id = '.$id.$limit_sql);
		$data = $sql->result_object();
		$num_rows = $sql->num_rows();
		if($num_rows == 0)
		{
			return (object)array('status' => 404,'content' => array(), 'description' => 'Không tồn tại danh sách bình luận cho coupon này');
		}
        return (object)array('status' => 200,'content' => $data, 'description' => 'Danh sách coupon');
    }

	
	public function userGetCoupon($user_id,$coupon_id){
		$check_user = $this->db->query('SELECT * FROM member WHERE id = '.$user_id)->num_rows(); // check người dùng có tồn tại không
		$check_coupon_expried = $this->db->query('SELECT id FROM ecoupon WHERE id = '.$coupon_id.' AND `created` < "'.date('Y-m-d H:i:s').'"' )->num_rows(); // check coupon có quá hạn không
		$coupon_amount = $this->db->query('SELECT amount FROM ecoupon WHERE id = '.$coupon_id)->row()->amount; // số coupon phát ra
		$coupon_used = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available; // số coupon đã dùng
		$check_coupon_user = $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id IS NULL ')->num_rows(); // check xem coupon phát ra đã nhận hay dùng hết chưa.
		if ( $check_user == 0 || $check_coupon_expried == 0 || ( $coupon_amount ) <= ( $coupon_used ) || ( $check_coupon_user == 0 )
		){
			return (object)array('status' => 201, 'description' => 'Người dùng không tồn tại/Coupon không tồn tại-hết hạn/Coupon đã sử dụng hết', 'status_sync' => 201, 'content_sync' => 'Đồng bộ thất bại');
		}else{
			
			//check xem coupon đã nhận chưa
			if ( $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id = '.$user_id.' AND active != 2' )->num_rows() != 0 ){
				// nếu đã nhận
				$code_coupon = $this->db->query('SELECT code_coupon FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id = '.$user_id)->row()->code_coupon;
				return (object)array('status' => 202, 'description' => 'Coupon này đã nhận', 'code_coupon' => $code_coupon, 'status_sync' => 201, 'content_sync' => 'Đồng bộ thất bại' );
			}
			//chưa nhận
			$id_ecoupon_user = $this->db->query('SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ')->row()->id;
			$code_coupon = $this->db->query('SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ')->row()->code_coupon;
			$run = $this->db->update('ecoupon_user', array( 'user_id' => $user_id, 'active' => 1, 'created' => date('Y-m-d H:i:s') ) , 'id = '.$id_ecoupon_user); 
			$this->sendMailUser($user_id, $code_coupon, $id_ecoupon_user);
            if($run){
				// update so coupon da dung
				$amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available;
				$amount_available = (int)$amount_available + 1;
				$this->db->update('ecoupon', array( 'amount_available' => $amount_available ) , 'id = '.$coupon_id);
				// đồng bộ sang bên wicard
				$this->syncGetCouponWicard($user_id,$coupon_id,$code_coupon);
                return (object)array('status' => 200, 'description' => 'Nhận coupon thành công', 'code_coupon' => $code_coupon );
            }else{
                return (object)array('status' => 203, 'description' => 'Nhận coupon thất bại');
            }
		}
	}
 	
	private function sendMailUser($user_id, $code_coupon, $id_ecoupon_user)
    {
		$db = $this->db->query('SELECT username,fullname FROM member WHERE id = '.$user_id.' LIMIT 1')->row();
        $datacp = $this->db->query('SELECT e.title,e.note_email,e.subject_email FROM ecoupon AS e LEFT JOIN ecoupon_user AS eu ON e.id = eu.coupon_id WHERE eu.id = '.$id_ecoupon_user.' LIMIT 1')->row(); 
        $config = array(
						'protocol' => 'smtp',
						'smtp_host' => $this->_smtp_host,
						'smtp_user' => $this->_smtp_user,
						'smtp_pass' => $this->_smtp_pass,
						'mailtype' => 'html'
                    );
        $this->load->library('email',$config);
        $body    = "<img src='http://125.212.192.114/ggift/data/images/other/mail-header-gg.jpg' style='width: 825px;'/><br/><br/>Chúc mừng bạn đã đăng ký nhận ".$datacp->title." thành công! <br />Mã E-coupon của bạn là: <b>".$code_coupon."</b><br /><br />".html_entity_decode(stripslashes($datacp->note_email), ENT_QUOTES, "UTF-8")."<br/><br/><img src='http://125.212.192.114/ggift/data/images/other/mail-footer-gg.jpg' style='width: 825px;'/>"; 
        $this->email->from($this->_smtp_user, 'G-People');
        $this->email->to($db->username);    
        $this->email->subject($datacp->subject_email);
        $this->email->message($body);    
        if($this->email->send()){
        }
    }
	
	public function checkDelCouponUser(){
		
		$data = $this->db->query('SELECT id,created,coupon_id FROM ecoupon_user WHERE active = 1')->result_object();
		foreach ( $data AS $el=>$le ){
			$time_created = strtotime(date('Y-m-d', strtotime($le->created)));
			$time = strtotime("+5 day", $time_created);
			if( time() > $time){ // het han
				$this->db->update('ecoupon_user', array( 'user_id' => null, 'active' => (int)0, 'created' => null  ) , 'id = '.$le->id);
				$amount_available = (int)$this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$le->coupon_id)->row()->amount_available - 1;
				$this->db->update('ecoupon', array('amount_available' => $amount_available),'id ='.$le->coupon_id);
			}
		}
		return 200;
	}
	
	public function syncGetCouponWicard($user_id,$gg_coupon_id,$code_coupon){
		$this->db_b = $this->load->database('wicard', TRUE);
        $wi_coupon_id = $this->db_b->query(' SELECT id FROM ecoupon WHERE id_copy = '.$gg_coupon_id.' ')->row_object()->id;
		if( in_array($wi_coupon_id,array('',null)) ){
			//return (object)array('status' => 200, 'description' => 'Nhận coupon thành công', 'code_coupon' => $code_coupon, 'status_sync' => 201, 'content_sync' => 'Ecoupon bên Wicard không tồn tại' );
		}else{
			$data_wi_coupon = $this->db_b->query(' SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = '.$wi_coupon_id.' AND user_id is NULL AND active = 0 ORDER BY id ASC LIMIT 1 ');
			if( $data_wi_coupon->num_rows() == 0 ){
				//return (object)array('status' => 200, 'description' => 'Nhận coupon thành công', 'code_coupon' => $code_coupon, 'status_sync' => 202, 'content_sync' => 'Ecoupon bên Wicard đã nhận hoặc sử dụng hết' );
			}else{
				$wi_cp_id = $data_wi_coupon->row_object()->id;
				$wi_cp_code = $data_wi_coupon->row_object()->code_coupon;
				$data_update = array(
						'user_id' => 1,  // default là 1
						'active' => 1,  // nhận là 1
						'created' => date('Y-m-d H:i:s') // thời gian nhận
						);
				$run = $this->db_b->update('ecoupon_user', $data_update,'id = '.$wi_cp_id);
				//
				if($run){
					// update so coupon da dung wicard
					$wi_amount_available = $this->db_b->query('SELECT amount_available FROM ecoupon WHERE id = '.$wi_coupon_id)->row()->amount_available;
					$wi_amount_available = (int)$wi_amount_available + 1;
					$this->db_b->update('ecoupon', array( 'amount_available' => $wi_amount_available ) , 'id = '.$wi_coupon_id);
					//return (object)array('status' => 200, 'description' => 'Nhận coupon thành công', 'code_coupon' => $code_coupon, 'status_sync' => 200, 'content_sync' => 'Ecoupon bên Wicard đồng bộ thành công', 'code_coupon_sync' => $wi_cp_code );
				}
				//
				
			}
		}
	}
    public function userGetCouponOther($user_id, $coupon_id)
    {
        if ($this->db->query('SELECT id FROM member WHERE id = ' . $user_id)->num_rows() == 0 ||
                $this->db->query('SELECT id FROM ecoupon WHERE id = ' . $coupon_id . ' AND `created` < "' . date('Y-m-d H:i:s') . '"')->num_rows() == 0 ||
                ( $this->db->query('SELECT amount FROM ecoupon WHERE id = ' . $coupon_id)->row()->amount) <= ($this->db->query('SELECT amount_available FROM ecoupon WHERE id = ' . $coupon_id)->row()->amount_available) ||
                ( $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id IS NULL ')->num_rows() == 0 )
        )
        {
            return (object) array('status' => 201, 'description' => 'Người dùng không tồn tại/Coupon không tồn tại/Coupon hết hạn/Coupon đã sử dụng hết');
        } else
        {
            if ($this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id = ' . $user_id . ' AND active != 2')->num_rows() != 0)
            {
                $code_coupon = $this->db->query('SELECT code_coupon FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id = ' . $user_id)->row()->code_coupon;
                return (object) array('status' => 202, 'description' => 'Coupon này đã nhận', 'code_coupon' => $code_coupon);
            }
            $id_ecoupon_user = $this->db->query('SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ')->row()->id;
            $code_coupon = $this->db->query('SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ')->row()->code_coupon;

            //$data = array ( 'user_id' => $user_id, 'coupon_id' => $coupon_id, 'code_coupon' => $code_coupon , 'active' => 1, 'created' => date('Y-m-d H:i:s') );
            $run = $this->db->update('ecoupon_user', array('user_id' => $user_id, 'active' => 1, 'created' => date('Y-m-d H:i:s')), 'id = ' . $id_ecoupon_user);

            $this->sendMailUser($user_id, $code_coupon, $id_ecoupon_user);
            if ($run)
            {
                // update so coupon da dung
                $amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = ' . $coupon_id)->row()->amount_available;
                $amount_available = (int) $amount_available + 1;
                $this->db->update('ecoupon', array('amount_available' => $amount_available), 'id = ' . $coupon_id);
                return (object) array('status' => 200, 'description' => 'Thêm thành công', 'code_coupon' => $code_coupon);
            } else
            {
                return (object) array('status' => 203, 'description' => 'Thêm thất bại');
            }
        }
    }

}
